package com.example.permission.common.utils;

import org.apache.commons.lang3.StringUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * poi excel导入导出（支持大批量数据）
 *
 * @author xuwenqian
 * @date 2021/9/16 16:59
 */
public class ExcelPoiUtil {

    private static final Logger logger = LogManager.getLogger(ExcelPoiUtil.class);

    public static final DateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    public static final DecimalFormat df = new DecimalFormat("0.00");
    private static final String[] XLS_XLSX_CSV = {"xls", "xlsx", "csv"};

    /**
     * 导出excel
     *
     * @param response
     * @param fileName  创建名称
     * @param titleList 标题
     * @param fieldList 字段
     * @param dataList  数据
     */
    public static void exportExcel(HttpServletResponse response, String fileName, List<String> titleList, List<String> fieldList, List<?> dataList) {
        SXSSFWorkbook wb = ExcelPoiUtil.getWorkbook(fileName, titleList, fieldList, dataList);
        // 响应到客户端
        ExcelPoiUtil.setResponseHeader(response, fileName);
        try (OutputStream os = response.getOutputStream()) {
            wb.write(os);
            os.flush();
            wb.dispose();
        } catch (IOException e) {
            e.printStackTrace();
            wb.dispose();
        }
    }

    /**
     * 创建excel sheet
     *
     * @param sheetName sheet名称
     * @param titleList 标题
     * @param fieldList 字段
     * @param dataList  数据
     * @return
     */
    private static SXSSFWorkbook getWorkbook(String sheetName, List<String> titleList, List<String> fieldList, List<?> dataList) {
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        workbook.setCompressTempFiles(true);
        SXSSFSheet sheet = workbook.createSheet(sheetName);
        SXSSFRow row = sheet.createRow(0);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        SXSSFCell cell;
        //创建标题
        for (int i = 0; i < titleList.size(); i++) {
            cell = row.createCell(i);
            cell.setCellValue(titleList.get(i));
            cell.setCellStyle(cellStyle);
        }
        //创建内容
        for (int i = 0; i < dataList.size(); ++i) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < fieldList.size(); ++j) {
                Object o = getFieldValue(dataList.get(i), fieldList.get(j));
                if (o == null || "-1".equals(o.toString())) {
                    continue;
                }
                setCellValue(row, j, o);
            }
        }
        return workbook;
    }

    /**
     * 写入内容
     *
     * @param row
     * @param j
     * @param o
     */
    private static void setCellValue(SXSSFRow row, int j, Object o) {
        if (Integer.class.isAssignableFrom(o.getClass())) {
            row.createCell(j).setCellValue(o.toString());
        } else if (Number.class.isAssignableFrom(o.getClass())) {
            row.createCell(j).setCellValue(df.format(o));
        } else if (Date.class.isAssignableFrom(o.getClass())) {
            row.createCell(j).setCellValue(fmt.format(o) + "\t");
        } else {
            row.createCell(j).setCellValue(o.toString());
        }
    }

    private static Object getFieldValue(Object obj, String fieldName) {
        Field field = getAccessibleField(obj, fieldName);
        if (field == null) {
            throw new IllegalArgumentException("no field [" + fieldName + "]");
        }
        try {
            Method method = obj.getClass().getMethod("get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1), (Class[]) null);
            return method.invoke(obj, (Object[]) null);
        } catch (Exception var5) {
            var5.printStackTrace();
            return null;
        }
    }

    private static Field getAccessibleField(Object obj, String fieldName) {
        Class superClass = obj.getClass();
        while (superClass != Object.class) {
            try {
                Field field = superClass.getDeclaredField(fieldName);
                field.setAccessible(true);
                return field;
            } catch (NoSuchFieldException var4) {
                var4.printStackTrace();
                superClass = superClass.getSuperclass();
            }
        }
        return null;
    }

    /**
     * 返回给前端
     *
     * @param response
     * @param fileName
     */
    private static void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            fileName = new String(fileName.getBytes(), StandardCharsets.UTF_8);
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }


    /**
     * 导入excel表格.返回的是List<Object> 的数据结构,使用反射去赋值.
     *
     * @param file       导入的文件
     * @param clazz      存储数据的实体 实体类中做映射，example:
     *                   public static String convert(String str){
     *                   switch (str){
     *                   case "身份证号码": return  "idNumber";
     *                   case "名称":  return "employeeName";
     *                   case "年龄":  return "age";
     *                   case "出生日期":  return "birthDate";
     *                   case "性别":  return "gender";
     *                   default: return  str;
     *                   }
     *                   }
     * @param ignoreRows 忽略标题行，0开始
     * @return {@link List<T> }
     * @author mozhu
     * @date 2021-09-18 17:05:50
     */
    public static <T> List<T> readExcel(MultipartFile file, Class<T> clazz, int ignoreRows) {
        try {
            if (file == null) {
                return null;
            }
            //最终返回数据
            List<T> resultList = new ArrayList<T>();
            InputStream is = file.getInputStream();
            String fileName = file.getOriginalFilename().toLowerCase();
            // 获取Excel后缀名
            String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
            if (!Arrays.asList(XLS_XLSX_CSV).contains(fileType)) {
                return null;
            }
            //使用工厂方法创建.
            Workbook workbook = WorkbookFactory.create(is);
            //得到一个工作表
            Sheet sheet = workbook.getSheetAt(0);
            //获得数据的总行数
            int totalRowNum = sheet.getLastRowNum();
            //获得总列数
            int cellLength = sheet.getRow(0).getPhysicalNumberOfCells();
            //获取表头
            Row firstRow = sheet.getRow(ignoreRows);
            //获取反射类的所有字段
            Field[] fields = clazz.getDeclaredFields();
            //创建一个字段数组,用于和excel行顺序一致.
            Field[] newFields = new Field[cellLength];
            //获取静态方法.该方法是用于把excel的表头映射成实体字段名.
            // convert方法是在反射的类中的静态方法.也防止Excel中的表头
            //和反射的类的字段顺序不一致,导致数据读取在不正确的属性上
            Method m = clazz.getDeclaredMethod("convert", String.class);
            Object ob = clazz.newInstance();
            for (int i = 0; i < cellLength; i++) {
                for (Field field : fields) {
                    Cell cell = firstRow.getCell(i);
                    //按照excel中的存储存放数组,以便后面遍历excel表格,数据一一对应.
                    if (m.invoke(ob, getXCellVal(cell)).equals(field.getName())) {
                        newFields[i] = field;
                    }
                }
            }
            //从第x行开始获取
            for (int x = 1; x <= totalRowNum; x++) {
                T object = clazz.newInstance();
                //获得第i行对象
                Row row = sheet.getRow(x);
                //如果一行里的所有单元格都为空则不放进list里面
                int a = 0;
                for (int y = 0; y < cellLength; y++) {
                    if (row != null) {
                        Cell cell = row.getCell(y);
                        if (cell == null) {
                            a++;
                        } else {
                            Field field = newFields[y];
                            String value = getXCellVal(cell);
                            if (StringUtils.isNotBlank(value)) {
                                //给字段设置值.
                                setValue(field, value, object);
                            }
                        }
                    }
                }
                if (a != cellLength && row != null) {
                    resultList.add(object);
                }
            }
            return resultList;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }


    /**
     * 导入excel表格,返回的List<Map<Integer, String>>数据结构.
     *
     * @param file       文件
     * @param ignoreRows 忽视行数 默认0
     * @return {@link List<Map<Integer, String>> }
     * @author mozhu
     * @date 2021-09-18 17:07:25
     */
    public static List<Map<Integer, String>> readExcel(MultipartFile file, int ignoreRows) {
        try {
            //最终返回数据
            List<Map<Integer, String>> list = new ArrayList<Map<Integer, String>>();
            InputStream is = file.getInputStream();
            String fileName = file.getOriginalFilename().toLowerCase();
            // 获取Excel后缀名
            String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
            if (!Arrays.asList(XLS_XLSX_CSV).contains(fileType)) {
                return null;
            }
            // 创建excel操作对象
            Workbook workbook = WorkbookFactory.create(is);
            //得到一个工作表
            Sheet sheet = workbook.getSheetAt(0);
            //获得数据的总行数
            int totalRowNum = sheet.getLastRowNum();

            //获得总列数
            int cellLength = sheet.getRow(0).getPhysicalNumberOfCells();
            Map<Integer, String> map = null;
            //获得所有数据
            //从第x行开始获取
            for (int x = ignoreRows; x <= totalRowNum; x++) {
                map = new HashMap<>();
                //获得第i行对象
                Row row = sheet.getRow(x);
                //如果一行里的所有单元格都为空则不放进list里面
                int a = 0;
                for (int y = 0; y < cellLength; y++) {
                    if (!(row == null)) {
                        Cell cell = row.getCell(y);
                        if (cell == null) {
                            map.put(y, "");
                        } else {
                            map.put(y, getXCellVal(cell));
                        }
                        if (map.get(y) == null || "".equals(map.get(y))) {
                            a++;
                        }
                    }
                }

                if (a != cellLength && row != null) {
                    list.add(map);
                }
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }


    /**
     * 给字段赋值,判断值的类型,然后转化成实体需要的类型值.
     *
     * @param field  字段
     * @param value  值
     * @param object 对象
     */
    private static void setValue(Field field, String value, Object object) {
        try {
            field.setAccessible(true);
            if (Integer.class.isAssignableFrom(field.getType())) {
                field.set(object, Integer.valueOf(value));
            } else if (Number.class.isAssignableFrom(field.getType())) {
                field.set(object, df.format(value));
            } else if (Date.class.isAssignableFrom(field.getType())) {
                field.set(object, fmt.parse(value));
            } else {
                field.set(object, value);
            }
            field.setAccessible(false);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * @param cell
     * @return String
     * 获取单元格中的值
     */

    private static String getXCellVal(Cell cell) {
        String val = "";
        switch (cell.getCellType()) {
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    //日期型
                    val = fmt.format(cell.getDateCellValue());
                } else {
                    //数字型
                    val = df.format(cell.getNumericCellValue());
                    // 去掉多余的0，如最后一位是.则去掉
                    val = val.replaceAll("0+?$", "").replaceAll("[.]$", "");
                }
                break;
            case STRING:
                //文本类型
                val = cell.getStringCellValue();
                break;
            case BOOLEAN:
                //布尔型
                val = String.valueOf(cell.getBooleanCellValue());
                break;
            case BLANK:
                //空白
                val = cell.getStringCellValue();
                break;
            case ERROR:
                //错误
                val = "";
                break;
            case FORMULA:
                //公式
                try {
                    val = String.valueOf(cell.getStringCellValue());
                } catch (IllegalStateException e) {
                    val = String.valueOf(cell.getNumericCellValue());
                }
                break;
            default:
                val = cell.getRichStringCellValue() == null ? null : cell.getRichStringCellValue().toString();
        }
        return val;
    }

}
